# coding=UTF-8
'''
Created on 2020-07-10 17:53
@author: jin
'''
import MySQLdb
from lxml import etree
import requests
import json
import time
from decimal import Decimal


def get_data_from_json(url, data_type):
    response = requests.request('get', url)
    print(response.encoding)
    json_text = response.text;
    type = typeof(json_text)
    if (type == "str"):
        json_text = json_text[json_text.index('['):json_text.index(']') + 1]
    return json.loads(json_text)


def get_data_from_html(url, xpaths):
    headers = {
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36'
    }
    response = requests.request('get', url, headers=headers)
    print(response.encoding)
    fund_xpath = etree.HTML(response.content, parser=etree.HTMLParser(encoding='utf8'))
    re = {}
    data = []
    for k in xpaths.keys():
        re[k] = fund_xpath.xpath(xpaths[k])
        i = 0
        for col in re[k]:
            print(col)
            if (len(data) > i):
                data[i][k] = col
            else:
                data.append({k: col})
            i += 1
    return data


def typeof(variate):
    type = None
    if isinstance(variate, int):
        type = "int"
    elif isinstance(variate, str):
        type = "str"
    elif isinstance(variate, float):
        type = "float"
    elif isinstance(variate, list):
        type = "list"
    elif isinstance(variate, tuple):
        type = "tuple"
    elif isinstance(variate, dict):
        type = "dict"
    elif isinstance(variate, set):
        type = "set"
    return type


def save_data(db, sql):
    print(sql)
    cursor = db.cursor()
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()


def find_data(db, sql):
    # print(sql)
    cursor = db.cursor()
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        # 打印结果
        return results
    except:
        print("Error: unable to fecth data")


def get_data_to_mysql(data, db, qz, data_type, bz):
    for value in data:
        values = {}
        if (typeof(data_type) == 'dict'):
            values = value
            print(value)
            if 'FCODE' not in value.keys():
                continue
            if 'SHORTNAME' not in value.keys():
                value['SHORTNAME'] = ''
            if 'DWJZ' not in value.keys():
                value['DWJZ'] = ''
            if 'FUNDTYPE' not in value.keys():
                value['FUNDTYPE'] = ''
            if 'type_zh' not in value.keys():
                value['type_zh'] = ''
            else:
                value['type_zh'] = ''
        if (typeof(data_type) == 'list'):
            v = value.split(',')
            list_to_dict(data_type, v, values,'FCODE',0)
            list_to_dict(data_type, v, values,'SHORTNAME',1)
            list_to_dict(data_type, v, values,'DWJZ',2)
            list_to_dict(data_type, v, values,'FUNDTYPE',3)
            list_to_dict(data_type, v, values,'type_zh',4)
        row_save(db, qz, values, bz)


def list_to_dict(data_type, v, value,str,i):
    if (typeof(data_type[i]) == 'int'):
        value[str] = v[data_type[i]]
    else:
        value[str] = data_type[i]


def row_save(db, qz, value, bz):
    if(not value):
        return
    sql = "select * from my_fund_good where fund_code='%s'" % (value["FCODE"])
    sql_data = find_data(db, sql)
    if (sql_data):
        plan = sql_data[0][3] + Decimal(str(qz))
        print(str(sql_data[0][3])+","+str(Decimal(str(qz)))+"="+str(plan))
        bz1 = bz
        if (sql_data[0][4]):
            bz1 = sql_data[0][4] + bz
        type_zh=value["type_zh"]
        if( value["type_zh"] not in sql_data[0][5]):
            type_zh=sql_data[0][5]+","+type_zh
        sql = "UPDATE my_fund_good SET plan = '%s',bz= '%s' ,update_date='%s' ,price='%s',type_zh='%s' WHERE fund_code = '%s'" \
              % (plan, bz1, time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), value["DWJZ"],type_zh, value["FCODE"])
    else:
        sql = """INSERT INTO my_fund_good(fund_code,fund_name,
         plan, bz, update_date, price,type,type_zh)
         VALUES ('%s', '%s',%s, '%s', '%s', '%s','%s','%s')""" % (value["FCODE"], value["SHORTNAME"], qz, bz,
                                                                  time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                                                                  value["DWJZ"], value["FUNDTYPE"],value["type_zh"])
    save_data(db, sql)


if __name__ == "__main__":
    # print(data)
    db = MySQLdb.connect("localhost", "root", "123456", "fund", charset='utf8')
    url = 'https://fundzt.eastmoney.com/2017/newwxph/funds.js'
    data = get_data_from_json(url,'')
    get_data_to_mysql(data, db, 1.5, {},"天天三年五星；")

    url = "http://fund.eastmoney.com/topic.html"
    xpaths = {'FCODE': '//*[@class="m-fdlist"]/table/tr/td/p[1]/span[1]/text()'
        , 'SHORTNAME': '//*[@class="m-fdlist"]/table/tr/td/p[1]/a[1]/text()'}
    data = get_data_from_html(url, xpaths)
    get_data_to_mysql(data, db, 2, {}, "天天优选基金；")

    url = "http://fund.eastmoney.com/data/FundGuideapi.aspx?dt=0&sd=&ed=&rt=zs,5&se=2&sc=1n&st=desc&pi=1&pn=20&zf=diy&sh=list&rnd=0.7032841093197962"
    data = get_data_from_json(url,'')
    get_data_to_mysql(data, db, 1.2, [0,1,18,'',3], "招商评级五星；")
    url = "http://fund.eastmoney.com/data/FundGuideapi.aspx?dt=0&sd=&ed=&rt=zs,5&se=2&sc=1n&st=desc&pi=2&pn=20&zf=diy&sh=list&rnd=0.9370789710425764"
    data = get_data_from_json(url,'')
    get_data_to_mysql(data, db, 1.2, 'list', "招商评级五星；")
    for i in range(1,5):
        url="http://fund.eastmoney.com/data/FundGuideapi.aspx?dt=0&sd=&ed=&rt=sz,5&se=2&sc=1n&st=desc&pi="+str(i)+"&pn=20&zf=diy&sh=list&rnd=0.40447402368890406"
        data = get_data_from_json(url,'')
        get_data_to_mysql(data, db, 1, [0,1,18,'',3], "上证评级五星；")
    for i in range(1,5):
        url ="http://fund.eastmoney.com/data/FundGuideapi.aspx?dt=0&sd=&ed=&rt=ja,5&se=2&sc=1n&st=desc&pi="+str(i)+"&pn=20&zf=diy&sh=list&rnd=0.6743737978364042"
        data = get_data_from_json(url,'')
        get_data_to_mysql(data, db, 1, [0,1,18,'',3], "济安评级五星；")
    for i in range(1,5):
        url ="http://fund.eastmoney.com/data/FundGuideapi.aspx?dt=0&rs=2n,100&sd=&ed=&sc=2n&st=desc&pi="+str(i)+"&pn=20&zf=diy&sh=list&rnd=0.7665544954850689"
        data = get_data_from_json(url,'')
        get_data_to_mysql(data, db, 1.8, [0,1,18,'',3], "两年前100；")
    for i in range(1,5):
        url ="http://fund.eastmoney.com/data/FundGuideapi.aspx?dt=0&rs=jn,100&sd=&ed=&sc=jn&st=desc&pi="+str(i)+"&pn=20&zf=diy&sh=list&rnd=0.40082351478998723"
        data = get_data_from_json(url,'')
        get_data_to_mysql(data, db, 1, [0,1,18,'',3], "一年前100；")


    # url ="http://fund.eastmoney.com/fundhot8.html"
    # xpaths = {'FCODE': '//div[contains(@class,"clearfix")]/div/table/tbody/tr/td[2]/a/@href'
    #     , 'SHORTNAME': '//div[contains(@class,"clearfix")]/div/table/tbody/tr/td[2]/a/text()'}
    # data = get_data_from_html(url, xpaths)
    # get_data_to_mysql(data, db, 1, 'map', "天天热销基金；")

    # sql = "select * from my_fund_good where fund_code='%s'" % ("210011")
    # sql_data = find_data(db, sql)
    # print(sql_data)
    # sql="""UPDATE my_fund_good SET plan = 1,bz= '天天三年五星；' ,update_date='2020-07-04 08:59:00' ,price='' WHERE fund_code = '210011'"""
    # save_data(db,sql)
